package cn.vastbase.jdbc.base;

import org.junit.Test;

import java.sql.*;

/**
 * <p>基础操作示例：包括增、删、改、查、建表，普通SQL、预编译SQL </p>
 * <p>适用于Vastbase G100 2.2 Build 15.4 </p>
 */
public class CrudTestCase {

    /* 定义连接对象 */
    static Connection conn = null;
    static String cname = "org.postgresql.Driver";
    static String url = "jdbc:postgresql://192.168.56.12:6000/test?characterEncoding=GBK&allowEncodingChanges=true&loggerLevel=trace&autosave=always&cleanupSavepoints=ture";
    static String username = "zxs";
    static String passwd = "Vastdata#0730";
    /* 定义 SQL 语句执行对象 */
    static Statement stmt = null;
    static PreparedStatement pstmt = null;
    /* 定义结果集对象 */
    static ResultSet rs = null;

    public Connection conn() {
        try {
            Class.forName(cname);
            return conn = DriverManager.getConnection(url, username, passwd);
        } catch (ClassNotFoundException | SQLException e) {
           e.printStackTrace();
        }
        return null;

    }


    public void close() {
        try {
            /* 关闭结果集对象 */
            if (rs != null)
                rs.close();
        } catch (SQLException e) {
            System.out.println("[FAIL] close resultSet fail." + e.getMessage());
        }
        try {
            /* 关闭语句对象 */
            if (stmt != null)
                stmt.close();
        } catch (SQLException e) {
            System.out.println("[FAIL] close statement fail." + e.getMessage());
        }
        try {
            /* 关闭语句对象 */
            if (pstmt != null)
                pstmt.close();
        } catch (SQLException e) {
            System.out.println("[FAIL] close statement fail." + e.getMessage());
        }
        try {
            /* 关闭连接 */
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            System.out.println("[FAIL] close connection fail." + e.getMessage());
        }
    }

    @Test
    public void create() {
        try {
            /* 初始化连接 */
            if (conn() == null)
                return;

            /* 创建语句对象 */
            stmt = conn.createStatement();

            int rc = stmt.executeUpdate("DROP TABLE IF EXISTS t_fruit;" +
                    "CREATE TABLE t_fruit(fruit_id INTEGER, fruit_name VARCHAR(32))");

            if (rc != 0) {
                System.out.println("[FAIL] fail to create table.\n");
            } else {
                System.out.println("[SUCCESS] create table successfully.\n");
            }
            /* 关闭语句对象 */
            stmt.close();
            /* 关闭连接 */
            conn.close();
        } catch (SQLException e) {
            System.out.println("[FAIL] execute sql fail." + e.getMessage());
        } finally {
            close();
        }
    }

    @Test
    public void createMultiSQL() {
        try {
            url = "jdbc:postgresql://192.168.56.12:6000/test?loggerLevel=OFF";

            /* 初始化连接 */
            if (conn() == null)
                return;

            /* 创建语句对象 */
            stmt = conn.createStatement();

            int rc = stmt.executeUpdate("DROP TABLE IF EXISTS t_fruit;" +
                    "CREATE TABLE t_fruit(fruit_id INTEGER, fruit_name VARCHAR(32));" +
                    "CREATE OR REPLACE FUNCTION add_two_numbers(num1 integer, num2 integer)\n" +
                    "RETURNS integer AS $$\n" +
                    "BEGIN\n" +
                    "  -- 返回两个输入参数的和\n" +
                    "  RETURN num1 + num2;\n" +
                    "END;\n" +
                    "$$ LANGUAGE plpgsql;");

            if (rc != 0) {
                System.out.println("[FAIL] fail to create table.\n");
            } else {
                System.out.println("[SUCCESS] create table successfully.\n");
            }
            /* 关闭语句对象 */
            stmt.close();
            /* 关闭连接 */
            conn.close();
        } catch (SQLException e) {
            System.out.println("[FAIL] execute sql fail." + e.getMessage());
        } finally {
            close();
        }
    }

    /**
     * create sequence test1115a_Id;
     * create table test1115a(id int primary key,fname varchar(50));
     * 2.10版本以下驱动不支持创建触发器，因为:会被解析为参数标识符，使用$替换，导致变成PBE SQL.
     * 如果设置preferQueryMode=simple可创建，因为不会解析参数
     */
    @Test
    public void createTrigger() {
        try {
            url = "jdbc:postgresql://172.16.55.175:5000/test?loggerLevel=trace";
            /* 初始化连接 */
            if (conn() == null)
                return;
            stmt = conn.createStatement();
            stmt.execute("CREATE OR REPLACE TRIGGER test1115a_Id_Tr     before insert on test1115a for each row begin select test1115a_Id.nextval into :new.id from dual;  end;");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
    }

    @Test
    public void createTrigger3() {
        try {
            url = "jdbc:postgresql://172.16.55.175:5000/test?loggerLevel=info&preferQueryMode=extended";
            /* 初始化连接 */
            if (conn() == null)
                return;
            stmt = conn.createStatement();
            stmt.execute("CREATE OR REPLACE TRIGGER DocdetailChg_search_TRIGGER AFTER UPDATE OR INSERT OR DELETE ON Docdetail FOR each row declare MODTYPE_1        VARCHAR2(4000); DOCCREATEDATE_1  VARCHAR2(10); DOCID            INTEGER; begin IF INSERTING THEN MODTYPE_1 := 'INSERT'; DOCCREATEDATE_1 := TO_CHAR(sysdate , 'YYYY-MM-DD'); DOCID := new.id; ELSIF UPDATING THEN MODTYPE_1 := 'UPDATE'; DOCCREATEDATE_1 := :old.DOCCREATEDATE; DOCID := :old.id; ELSIF DELETING THEN MODTYPE_1 := 'DELETE'; DOCCREATEDATE_1 := :old.DOCCREATEDATE; DOCID := :old.id; end if;  insert into indexupdatelog(ID, DOCID, CTYPE, MODTYPE, CREATETIME,DOCCREATEDATE, DONEFLAG) values(indexupdatelog_Id.nextval, DOCID, 'DOCSEARCH', MODTYPE_1, TO_CHAR(sysdate , 'YYYY-MM-DD HH24:MI:SS'),DOCCREATEDATE_1, 0); end;");
            stmt.executeUpdate("update docdetail set replyremind = 0 where id = 5");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
    }

    @Test
    public void createTrigger4() {
        try {
            url = "jdbc:postgresql://172.16.55.175:5000/test?loggerLevel=info&preferQueryMode=extended";
            /* 初始化连接 */
            if (conn() == null)
                return;
            pstmt = conn.prepareStatement("CREATE OR REPLACE TRIGGER DocdetailChg_search_TRIGGER AFTER UPDATE OR INSERT OR DELETE ON Docdetail FOR each row declare MODTYPE_1        VARCHAR2(4000); DOCCREATEDATE_1  VARCHAR2(10); DOCID            INTEGER; begin IF INSERTING THEN MODTYPE_1 := 'INSERT'; DOCCREATEDATE_1 := TO_CHAR(sysdate , 'YYYY-MM-DD'); DOCID := new.id; ELSIF UPDATING THEN MODTYPE_1 := 'UPDATE'; DOCCREATEDATE_1 := :old.DOCCREATEDATE; DOCID := :old.id; ELSIF DELETING THEN MODTYPE_1 := 'DELETE'; DOCCREATEDATE_1 := :old.DOCCREATEDATE; DOCID := :old.id; end if;  insert into indexupdatelog(ID, DOCID, CTYPE, MODTYPE, CREATETIME,DOCCREATEDATE, DONEFLAG) values(indexupdatelog_Id.nextval, DOCID, 'DOCSEARCH', MODTYPE_1, TO_CHAR(sysdate , 'YYYY-MM-DD HH24:MI:SS'),DOCCREATEDATE_1, 0); end;");
            pstmt.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
    }


    @Test
    public void insert() {
        try {
            cname = "org.postgresql.Driver";
            url = "jdbc:postgresql://192.168.56.17:6000/vastbase?loggerLevel=off&Stringtype=unspecified";
            username = "test";
            passwd = "Vastdata#0730";
            if (conn() == null)
                return;
//            conn.setAutoCommit(false);
            pstmt = conn.prepareStatement("INSERT INTO t3 (a) values  (?)");
            pstmt.setString(1, "4");
//
//            pstmt.setInt(1, 2);
//            pstmt.setString(2, "orange");
//            pstmt.addBatch();
//
//            pstmt.setInt(1, 3);
//            pstmt.setString(2, "banana");
//            pstmt.addBatch();
            pstmt.executeUpdate();

           /* for (int i = 1; i <= 1200; i++){
                pstmt = conn.prepareStatement("select * from  t_fruit limit 1");
                pstmt.executeQuery();
            }
            System.out.println("[SUCCESS] insert successfully.");*/
//            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
    }

    /**
     * create
     */
    @Test
    public void insertTimestampz() {
        cname = "org.postgresql.Driver";
        url = "jdbc:postgresql://192.168.56.12:6000/test?characterEncoding=GBK&allowEncodingChanges=true&loggerLevel=trace";
        username = "zxs";
        passwd = "Vastdata#0730";
        try {
            if (conn() == null)
                return;

            pstmt = conn.prepareStatement("insert into t3 values (?, ?)");
            pstmt.setString(1, "2024-12-16 10:02:12+02");
            pstmt.setString(2, "2024-12-16 10:02:11.725");
            pstmt.addBatch();
            pstmt.execute();

            System.out.println("[SUCCESS] insert successfully.");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
    }

    @Test
    public void insert2() {
        cname = "org.postgresql.Driver";
        url = "jdbc:postgresql://172.16.55.184:5432/testdb_mysql?characterEncoding=GBK&allowEncodingChanges=true&loggerLevel=off";
        username = "zxs";
        passwd = "Vastdata#0730";
        try {
            if (conn() == null)
                return;

            pstmt = conn.prepareStatement("insert into t2 values (1,'1')");
            pstmt.execute();

            System.out.println("[SUCCESS] insert successfully.");
        } catch (SQLException e) {
            System.out.println("ErrorCode:" + e.getErrorCode());
            System.out.println("SQLState:" + e.getSQLState());
            e.printStackTrace();
        } finally {
            close();
        }
    }

    @Test
    public void insert3() {
        cname = "org.opengauss.Driver";
        url = "jdbc:opengauss://192.168.56.12:5000/vastbase";
        username = "test";
        passwd = "Vastdata#0730";
        try {
            if (conn() == null)
                return;

            pstmt = conn.prepareStatement("insert into hi_sys_ro_user (id_rouser,id_tet,id_ro,id_user) values ('1','1','','1')");
            pstmt.execute();

            System.out.println("[SUCCESS] insert successfully.");
        } catch (SQLException e) {
            System.out.println("ErrorCode:" + e.getErrorCode());
            System.out.println("SQLState:" + e.getSQLState());
            e.printStackTrace();
        } finally {
            close();
        }
    }


    @Test
    public void select() {
        try {
            if (conn() == null)
                return;
            pstmt = conn.prepareStatement("select fruit_id, fruit_name from t_fruit");
            rs = pstmt.executeQuery();
            while (rs.next()) {
                System.out.println("t_fruit[fruit_id=" + rs.getInt(1) + ",fruit_name=" + rs.getString(2) + "]");
            }

        } catch (SQLException e) {
            System.out.println("[FAIL] select fail." + e.getMessage());
        } finally {
            close();
        }
    }

    @Test
    public void loop() {

    }

    @Test
    public void testQuery() {
        try {
            cname = "org.postgresql.Driver";
            url = "jdbc:postgresql://192.168.56.11:5432/vastbase?loggerLevel=none";
            username = "test";
            passwd = "Vastdata#0730";

            if (conn() == null)
                return;
            stmt = conn.createStatement();
            rs = stmt.executeQuery("select 1");
            pstmt = conn.prepareStatement("select 2");
            pstmt.executeQuery();
           /* ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            for (int i = 0; i < columnCount; i++) {
                System.out.println(metaData.getColumnName(i + 1));
                System.out.println(metaData.getColumnLabel(i + 1));
            }*/

        } catch (SQLException e) {
            System.out.println("[FAIL] select fail." + e.getMessage());
        } finally {
            close();
        }
    }

    @Test
    public void update() {
        try {
            if (conn() == null)
                return;
            pstmt = conn.prepareStatement("update t_fruit set fruit_name=? where fruit_id=?");
            pstmt.setString(1, "banana");
            pstmt.setInt(2, 1);
            pstmt.executeUpdate();
            System.out.println("[SUCCESS] update successfully.\n");
        } catch (SQLException e) {
            System.out.println("[FAIL] update fail." + e.getMessage());
        } finally {
            close();
        }
    }

    @Test
    public void delete() {
        try {
            if (conn() == null)
                return;
            pstmt = conn.prepareStatement("delete from t_fruit where fruit_id=?");
            pstmt.setInt(1, 1);
            pstmt.executeUpdate();
            System.out.println("[SUCCESS] delete successfully.");
        } catch (SQLException e) {
            System.out.println("[FAIL] delete fail." + e.getMessage());
        } finally {
            close();
        }
    }


}
